﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL_Builder
{
    public class Ado_Builder
    {

        public static Dictionary<string, string> fieldTypeDic = new Dictionary<string, string>();
        static Ado_Builder()
        {
            //uniqueidentifier
            //nvarchar
            //decimal
            //datetime
            //int
            //text            
            fieldTypeDic.Add("uniqueidentifier", "Guid");
            fieldTypeDic.Add("nvarchar", "string");
            fieldTypeDic.Add("varchar", "string");
            fieldTypeDic.Add("decimal", "decimal");
            fieldTypeDic.Add("datetime", "DateTime");
            fieldTypeDic.Add("int", "int");
            fieldTypeDic.Add("text", "string");
            fieldTypeDic.Add("nchar", "string");
            fieldTypeDic.Add("bigint", "long");
            fieldTypeDic.Add("float", "float");
            fieldTypeDic.Add("tinyint", "int");
            fieldTypeDic.Add("smallint", "int");

            fieldTypeDic.Add("image", "int");
            fieldTypeDic.Add("bit", "int");

        }
        /// <summary>
        /// 创建模型
        /// </summary>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        public static List<string> Build_Entity(TableInfo tableInfo)
        {
            List<string> result = new List<string>();
            result.Add(string.Format("public class {0}", tableInfo.Name));
            result.Add("{");

            foreach (var one in tableInfo.Fields)
            {
                result.Add(string.Format("public {0} {1} ", one.FieldType.Name, one.Name) + "{ get; set; }");
            }
            result.Add("}");
            return result;
        }


        /// <summary>
        /// 创建添加方法，
        /// mark 因为可能是自增字段，以及，可能是默认字段,以及插入时的空值。添加方法有大概率在生成之后需要修改
        /// </summary>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        public static List<string> Build_Add(TableInfo tableInfo)
        {
            List<string> result = new List<string>();

            result.Add(string.Format("public static bool Add{0}({1} one)", tableInfo.Name, tableInfo.Name));
            result.Add("{");

            string sql = "INSERT INTO " + tableInfo.FullName + " (";
            List<string> fieldNames = tableInfo.Fields.Select(v => v.Name).ToList();
            bool jumpPrimaryKey = false;
            if (tableInfo.PrimaryKeyName != null)
            {
                //Int32 
                var field = tableInfo.Fields.Where(v => v.Name == tableInfo.PrimaryKeyName).First();
                if (field.FieldType.Name == "Int32" || field.FieldType.Name == "Int16" || field.FieldType.Name == "Int64")
                {
                    fieldNames.Remove(field.Name);
                    jumpPrimaryKey = true;
                }
            }
            sql = sql + string.Join(",", fieldNames.ToArray()) + ") VALUES (";
            for (int i = 0; i < fieldNames.Count; i++)
            {
                fieldNames[i] = "@" + fieldNames[i];
            }

            sql = sql + string.Join(",", fieldNames.ToArray()) + ")";
            result.Add("bool result = false;");
            result.Add(string.Format("string sqlStr = \"{0}\";", sql));
            result.Add("using (SqlCommand cmd = new SqlCommand(sqlStr, conn))");
            result.Add("{");

            foreach (var one in tableInfo.Fields)
            {
                if (jumpPrimaryKey && one.Name != tableInfo.PrimaryKeyName)
                {
                    result.Add(string.Format("cmd.Parameters.AddWithValue(\"@{0}\",one.{1} );", one.Name, one.Name));
                }
            }
            result.Add("result=cmd.ExecuteNonQuery()==1;");
            result.Add("}");
            result.Add("return result;");
            result.Add("}");
            return result;
        }

        /// <summary>
        /// 删除方法，视条件而改动
        /// </summary>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        public static List<string> Build_Delete(TableInfo tableInfo, List<string> whereFields, string whereLink)
        {
            List<string> result = new List<string>();
            string fieldStr = "";
            foreach (var one in whereFields)
            {
                fieldStr = fieldStr + tableInfo.Fields.Where(v => v.Name == one).First().FieldType.Name + " " + one + ",";
            }
            fieldStr = fieldStr.Trim(',');

            result.Add(string.Format("public static bool Delete{0}({1})", tableInfo.Name, fieldStr));
            result.Add("{");
            result.Add("bool result = false;");
            string whereStr = "";
            for (int i = 0; i < whereFields.Count; i++)
            {
                if (i == (whereFields.Count - 1))
                {
                    whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} ";
                }
                else
                {
                    whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} " + whereLink;
                }

            }
            string sql = $"DELETE FROM {tableInfo.FullName} WHERE {whereStr}";
            result.Add(string.Format("string sqlStr = \"{0}\";", sql));
            result.Add("using (SqlCommand cmd = new SqlCommand(sqlStr, conn))");
            result.Add("{");
            foreach (var one in whereFields)
            {
                result.Add(string.Format("cmd.Parameters.AddWithValue(\"@{0}\",{1} );", one, one));
            }
            result.Add("result=cmd.ExecuteNonQuery()==1;");
            result.Add("}");
            result.Add("return result;");
            result.Add("}");
            return result;
        }


        //public static List<string> Build_Delete(TableInfo tableInfo)
        //{
        //    List<string> result = new List<string>();
        //    result.Add(string.Format("public static bool Delete{0}({1} one)", tableInfo.Name, tableInfo.Name));
        //    result.Add("{");
        //    result.Add("bool result = false;");
        //    string sql = string.Format("DELETE FROM {0} WHERE {1}=@{2}", tableInfo.FullName, tableInfo.PrimaryKeyName, tableInfo.PrimaryKeyName);
        //    result.Add(string.Format("string sqlStr = \"{0}\";", sql));
        //    result.Add("using (SqlCommand cmd = new SqlCommand(sqlStr, conn))");
        //    result.Add("{");
        //    result.Add(string.Format("cmd.Parameters.AddWithValue(\"@{0}\",one.{1} );", tableInfo.PrimaryKeyName, tableInfo.PrimaryKeyName));
        //    result.Add("result=cmd.ExecuteNonQuery()==1;");
        //    result.Add("}");
        //    result.Add("return result;");
        //    result.Add("}");
        //    return result;
        //}


        //update方法

        /// <summary>
        /// update方法生成之后必定 进行修改，因为set跟where
        /// </summary>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        public static List<string> Build_Update(TableInfo tableInfo, List<string> whereFields, string whereLink)
        {
            List<string> result = new List<string>();
            result.Add(string.Format("public static bool Update{0}({1} one)", tableInfo.Name, tableInfo.Name));
            result.Add("{");
            string sql = string.Format("UPDATE {0} Set ", tableInfo.FullName);
            //List<string> fieldNames = tableInfo.Fields.Where(v=>v.Name!=tableInfo.PrimaryKeyName).Select(v=>v.Name).ToList();
            List<string> fieldUpdate = tableInfo.Fields.Where(v => whereFields.Contains(v.Name) == false).Select(v => v.Name).ToList();
            if (tableInfo.PrimaryKeyName != null)
            {
                fieldUpdate.Remove(tableInfo.PrimaryKeyName);
            }
            for (int i = 0; i < fieldUpdate.Count; i++)
            {
                fieldUpdate[i] = fieldUpdate[i] + "=@" + fieldUpdate[i];
            }

            string whereStr = "";
            for (int i = 0; i < whereFields.Count; i++)
            {
                if (i == (whereFields.Count - 1))
                {
                    whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} ";
                }
                else
                {
                    whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} " + whereLink;
                }
            }

            sql = sql + string.Join(",", fieldUpdate.ToArray()) + " WHERE " + whereStr;
            result.Add("bool result = false;");
            result.Add(string.Format("string sqlStr = \"{0}\";", sql));
            result.Add("using (SqlCommand cmd = new SqlCommand(sqlStr, conn))");
            result.Add("{");

            if (tableInfo.PrimaryKeyName != null && (whereFields.Contains(tableInfo.PrimaryKeyName) == false))
            {
                var f = tableInfo.Fields.Where(v => v.Name == tableInfo.PrimaryKeyName).First();
                tableInfo.Fields.Remove(f);
            }
            foreach (var one in tableInfo.Fields)
            {

                if (one.FieldType.Name == "String")
                {
                    result.Add(string.Format("if (one.{0} == null)", one.Name));
                    result.Add("{");
                    result.Add(string.Format("cmd.Parameters.AddWithValue(\"@{0}\",DBNull.Value );", one.Name));
                    result.Add("}");
                    result.Add("else");
                    result.Add("{");
                    result.Add(string.Format("cmd.Parameters.AddWithValue(\"@{0}\",one.{1} );", one.Name, one.Name));
                    result.Add("}");
                }
                else
                {
                    result.Add(string.Format("cmd.Parameters.AddWithValue(\"@{0}\",one.{1} );", one.Name, one.Name));
                }
            }
            result.Add("result=cmd.ExecuteNonQuery()==1;");
            result.Add("}");
            result.Add("return result;");
            result.Add("}");
            return result;

        }



        /// <summary>
        /// 单个查询 
        /// </summary>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        public static List<string> Build_SelectOne(TableInfo tableInfo, List<string> whereFields, string whereLink)
        {
            List<string> result = new List<string>();
            string fieldStr = "";
            foreach (var one in whereFields)
            {
                fieldStr = fieldStr + tableInfo.Fields.Where(v => v.Name == one).First().FieldType.Name + " " + one + ",";
            }
            fieldStr = fieldStr.Trim(',');
            result.Add(string.Format("public static {0} SelectA{1}({2})", tableInfo.Name, tableInfo.Name, fieldStr));
            result.Add("{");
            result.Add($"List<{tableInfo.Name}> result = new List<{tableInfo.Name}>();");
            string whereStr = "";
            for (int i = 0; i < whereFields.Count; i++)
            {
                if (i == (whereFields.Count - 1))
                {
                    whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} ";
                }
                else
                {
                    whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} " + whereLink;
                }
            }
            string sql = string.Format("SELECT top 1 * FROM {0}(nolock) WHERE {1}", tableInfo.FullName, whereStr);
            result.Add(string.Format("string sqlStr = \"{0}\";", sql));
            result.Add("using (SqlCommand cmd = new SqlCommand(sqlStr, conn))");
            result.Add("{");
            foreach (var one in whereFields)
            {
                result.Add(string.Format("cmd.Parameters.AddWithValue(\"@{0}\",{1} );", one, one));
            }
            result.Add($"result = FillData<{tableInfo.Name}>(cmd);");
            result.Add("}");
            result.Add("if (result.Count > 0)");
            result.Add("{");
            result.Add("return result.First();");
            result.Add("}");
            result.Add("else");
            result.Add("{");
            result.Add(" return null;");
            result.Add("}");
            result.Add("}");
            return result;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        public static List<string> Build_SelectInPage(TableInfo tableInfo, List<string> whereFields, string whereLink)
        {
            List<string> result = new List<string>();
            string fieldStr = "";
            foreach (var one in whereFields)
            {
                fieldStr = fieldStr + ","+ tableInfo.Fields.Where(v => v.Name == one).First().FieldType.Name + " " + one ;
            }

            result.Add(string.Format("public static List<{0}> Select{1}InPage(int count, int pageIndex {2})", tableInfo.Name, tableInfo.Name, fieldStr));
            result.Add("{");
            result.Add(string.Format("List<{0}> result = new List<{0}>();", tableInfo.Name));
            //string sql = string.Format("SELECT TOP(@count) * FROM {0} WHERE {1} not in(SELECT TOP(@ignoreCount) {1} FROM {0} ORDER BY \" + orderField + \" DESC) ORDER BY \" + orderField + \" DESC", tableInfo.Name, tableInfo.PrimaryKeyName);
            string whereStr = "";
            if (whereFields.Count > 0)
            {
                whereStr = "where ";
                for (int i = 0; i < whereFields.Count; i++)
                {
                    if (i == (whereFields.Count - 1))
                    {
                        whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} ";
                    }
                    else
                    {
                        whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} " + whereLink;
                    }
                }
            }

            string sql = "select * from(select ROW_NUMBER() over(order by "+ tableInfo.PrimaryKeyName + " ) as rownumber ,* from "+ tableInfo.FullName + "(nolock)  "+ whereStr + ") as G) as myTable where rownumber>{(pageIndex - 1) * count} and rownumber<={pageIndex * count}";

            result.Add(string.Format("string sqlStr = $\"{0}\";", sql));
            result.Add("using (SqlCommand cmd = new SqlCommand(sqlStr, conn))");
            result.Add("{");

            result.Add("cmd.Parameters.AddWithValue(\"@count\",count );");
            result.Add("cmd.Parameters.AddWithValue(\"@ignoreCount\",ignoreCount );");
            foreach (var one in whereFields)
            {
                result.Add(string.Format("cmd.Parameters.AddWithValue(\"@{0}\",{1} );", one, one));
            }


            result.Add($"result = FillData<{tableInfo.Name}>(cmd);");
            result.Add("}");
            result.Add("return result;");
            result.Add("}");
            return result;
        }



        /// <summary>
        /// 获取 表 里面数据的数据量
        /// </summary>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        public static List<string> Build_SelectCount(TableInfo tableInfo, List<string> whereFields, string whereLink)
        {
            List<string> result = new List<string>();
            string fieldStr = "";
            foreach (var one in whereFields)
            {
                fieldStr = fieldStr + tableInfo.Fields.Where(v => v.Name == one).First().FieldType.Name + " " + one + ",";
            }
            fieldStr = fieldStr.Trim(',');
            string whereStr = "";
            if (whereFields.Count > 0)
            {
                whereStr = "where ";
                for (int i = 0; i < whereFields.Count; i++)
                {
                    if (i == (whereFields.Count - 1))
                    {
                        whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} ";
                    }
                    else
                    {
                        whereStr = whereStr + $" {whereFields[i]}=@{whereFields[i]} " + whereLink;
                    }
                }
            }
            result.Add(string.Format("public static int Select{0}Count({1})", tableInfo.Name, fieldStr));
            result.Add("{");
            result.Add("int result = 0;");
            string sql = string.Format("SELECT COUNT(*) FROM {0}(nolock) {1}", tableInfo.FullName, whereStr);
            result.Add(string.Format("string sqlStr = \"{0}\";", sql));
            result.Add("using (SqlCommand cmd = new SqlCommand(sqlStr, conn))");
            result.Add("{");
            foreach (var one in whereFields)
            {
                result.Add(string.Format("cmd.Parameters.AddWithValue(\"@{0}\",{1} );", one, one));
            }

            result.Add("var reader = cmd.ExecuteReader();");
            result.Add("while (reader.Read())");
            result.Add("{");
            result.Add("result = reader.GetInt32(0);");
            result.Add("}");
            result.Add("reader.Close();");
            result.Add("}");
            result.Add("return result;");
            result.Add("}");
            return result;
        }

    }
}
